package server.datasource;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import server.core.Checkpoint;
import server.dao.util.DatabaseConstants;
import server.idatasource.ICheckPointDataSource;

public class CheckPointDataSource implements ICheckPointDataSource {

	private Connection	connection;

	private List<List<String>> getResultList(ResultSet resultSet) throws SQLException {

		List<List<String>> resultList = new ArrayList<List<String>>();
		while (resultSet.next()) {
			List<String> result = new ArrayList<String>();
			int i = 1;
			while (i <= 5) {
				result.add(resultSet.getString(i));
				i++;
			}
			resultList.add(result);
		}
		return resultList;
	}

	private Connection getConnection() {

		try {
			Class.forName("org.gjt.mm.mysql.Driver").newInstance();
		} catch (Exception e) {
			e.printStackTrace();
		}
		try {
			connection = DriverManager.getConnection(DatabaseConstants.URL, DatabaseConstants.USERNAME, DatabaseConstants.PASSWORD);
		} catch (Exception e) {
			return null;
		}
		return connection;
	}

	/*Returns all checkpoints in a distance given by the argument.The arguments length and 
	 * width shows the users position. So the difference between a checkpoint's position and
	 * the given position should be less or equal to the distance. 
	 * If an error occurs, in the first elements username field 
	 * is returned "ERROR".*/
	@Override
	public List<List<String>> getNearestCheckpoints(int length, int width, int distance) {
		Checkpoint checkpoint = new Checkpoint();
		List<Checkpoint> checkpointList = new ArrayList<Checkpoint>();
		Statement statement;
		String query = "";
		ResultSet resultSet;
		List<List<String>> resultList = new ArrayList<List<String>>();

		try {
			//Create connection object
			connection = getConnection();
			//Create Statement object
			statement = connection.createStatement();
			query = "SELECT * FROM " + DatabaseConstants.CHECKPOINT_TABLE + " WHERE  = ";
			//Execute the query
			resultSet = statement.executeQuery(query);
			resultList = getResultList(resultSet);

		} catch (Exception e) {
		} finally {
			try {
				connection.close();
			} catch (Exception e) {
				checkpoint.setInformation("ERROR");
				checkpointList.add(0, checkpoint);
			}
		}
		return resultList;
	}

	/*Inserts a new checkpoint given in the argument, into the database. Returns a string, with the
	 * value "OK" if the operation was successful, else returns "ERROR".*/
	@Override
	public String insertCheckpoint(double width, double length, double height, String info) {
		Statement statement;
		String command = "";
		String error = "OK";

		try {
			//Create connection object
			connection = getConnection();
			//Create Statement object
			statement = connection.createStatement();
			command = "INSERT INTO " + DatabaseConstants.CHECKPOINT_TABLE + " VALUES (" + width + "," + length + "," + height + ", '" + info + "')";
			//Execute the command
			statement.executeUpdate(command);
		} catch (Exception e) {
		} finally {
			try {
				connection.close();
			} catch (Exception e) {
				error = e.toString();
			}
		}
		return error;
	}

	/*Deletes a checkpoint from database, having the username given in the argument. Returns a string, with the
	 * value "OK" if the operation was successful, else returns "ERROR".*/
	@Override
	public String deleteCheckpoint(int id) {
		Statement statement;
		String command = "";
		String error = "OK";

		try {
			//Create connection object
			connection = getConnection();
			//Create Statement object
			statement = connection.createStatement();
			command = "DELETE FROM " + DatabaseConstants.CHECKPOINT_TABLE + " WHERE " + DatabaseConstants.CHECKPOINT_TABLE_ID + " = " + id;
			//Execute the command
			statement.executeUpdate(command);
		} catch (Exception e) {
		} finally {
			try {
				connection.close();
			} catch (Exception e) {
				error = e.toString();
			}
		}
		return error;
	}

	/*Updates the checkpoint given in the argument, in the database. Returns a string, with the
	 * value "OK" if the operation was successful, else returns "ERROR".*/
	@Override
	public String updateCheckpoint(int id, double width, double length, double height, String info) {

		Statement statement;
		String command = "";
		String error = "OK";

		try {
			//Create connection object
			connection = getConnection();
			//Create Statement object
			statement = connection.createStatement();
			command = "UPDATE " + DatabaseConstants.CHECKPOINT_TABLE + " SET " + DatabaseConstants.CHECKPOINT_TABLE_WIDTH + " = " + width + "," + DatabaseConstants.CHECKPOINT_TABLE_LENGTH + " = " + length
					+ ", " + DatabaseConstants.CHECKPOINT_TABLE_HEIGHT + " = " + height + ", " + DatabaseConstants.CHECKPOINT_TABLE_INFO + " = '" + info + "' WHERE " + DatabaseConstants.CHECKPOINT_TABLE_ID
					+ " = " + id;
			//Execute the command
			statement.executeUpdate(command);
		} catch (Exception e) {
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				error = e.toString();
			}
		}
		return error;
	}
}
